﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DataAccess;
using System.Diagnostics;
namespace DataProcess
{
    public partial class ProductForm : Form
    {
        public ProductForm()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            var db = DbAccess.Db["bd_product"];
            DataTable dtTemp = db.ExecuteDataTable("SELECT mdid FROM bd_product WHERE is_delete=0 GROUP BY mdid HAVING (Count(mdid)>1)");
            if (dtTemp.Rows.Count > 0)
            {
                MessageBox.Show("未作废数据中有重复的mdid");
                return;
            }
            db.ExecuteNonQuery("SELECT mdid INTO rpt_mdid FROM bd_product GROUP BY mdid HAVING (Count(mdid)>1)");
            dtTemp = db.ExecuteDataTable("SELECT rpt_mdid.mdid FROM rpt_mdid LEFT JOIN bd_product ON rpt_mdid.mdid = bd_product.mdid WHERE bd_product.is_delete=0 AND bd_product.mdid Is Null");

            if (dtTemp.Rows.Count > 0)
            {
                MessageBox.Show("有部分重复的id未找到未作废的数据");
                return;
            }
            DataTable schema = new DataTable();
            db.ExecuteConnection(conn =>
            {
                schema = conn.GetSchema("Columns", new string[] { null, null, "bd_product", "原始mdid" });
            });
            if (schema.Rows.Count == 0)
            {
                db.ExecuteNonQuery("ALTER TABLE bd_product ADD COLUMN 原始mdid int");
            }

            db.ExecuteNonQuery("UPDATE bd_product INNER JOIN rpt_mdid ON bd_product.mdid = rpt_mdid.mdid SET bd_product.原始mdid = [bd_product].[mdid]");


            try
            {
                var maxid = Convert.ToInt32(db.ExecuteScalar("select max(mdid) from bd_product"));
                DataTable dtMdid = db.ExecuteDataTable("SELECT mdid FROM bd_product GROUP BY mdid");

                List<int> mdids = new List<int>();

                List<int> noIds = new List<int>();


                for (int i = 0; i < dtMdid.Rows.Count; i++)
                {
                    int tempId = Convert.ToInt32(dtMdid.Rows[i]["mdid"]);
                    mdids.Add(tempId);
                }
                for (int i = 1; i <= maxid; i++)
                {
                    if (!mdids.Contains(i))
                    {
                        noIds.Add(i);
                    }
                }

                dtTemp = db.ExecuteDataTable("SELECT bd_product.MDID发生器, bd_product.mdid FROM bd_product INNER JOIN rpt_mdid ON bd_product.mdid = rpt_mdid.mdid WHERE bd_product.is_delete=1 ORDER BY bd_product.mdid");
                for (int i = 0; i < dtTemp.Rows.Count; i++)
                {
                    db.ExecuteNonQuery("UPDATE bd_product SET mdid = @mdid where MDID发生器=@identity", new Parameter("mdid", noIds[i]), new Parameter("identity", dtTemp.Rows[i]["MDID发生器"]));
                }
            }
            catch (Exception)
            {

            }
            finally
            {
                db.ExecuteNonQuery("DROP TABLE rpt_mdid");
            }
            MessageBox.Show("完成！");
        }

        private void button2_Click(object sender, EventArgs e)
        {
            var db = DbAccess.Db["bd_product"];

            var maxid = Convert.ToInt32(db.ExecuteScalar("select max(mdid) from bd_product"));
            db.ExecuteNonQuery("CREATE TABLE seq (mdid int)");
            for (int i = 1; i <= maxid; i++)
            {
                db.ExecuteNonQuery("INSERT INTO [seq] (mdid) values (@mdid)", new Parameter("mdid", i));
            }
            MessageBox.Show("完成！");
        }

        private string[] fields = {"approvalno",
"ori_cname",
"other_cname",
"st_cname",
"批件库GenericName错误，由人工修改过的标记",
"ori_ename",
"st_ename",
"批件库GenericNameEn错误，由人工修改过的标记",
"ori_cbrand",
"st_cbrand",
"批件库BrandName错误，由人工修改过的标记",
"display_st_cbrand",
"ori_ebrand",
"st_ebrand",
"批件库BrandNameEn错误，由人工修改过的标记",
"display_st_ebrand",
"st_formid",
"ori_form_name",
"st_form_name",
"st_form_name",
"ori_strength",
"st_strength",
"ori_comp_cname",
"display_ori_comp_cname",
"st_compid",
"st_comp_name",
"批件库生产厂商(中文)错误，由人工修改过的标记",
"ori_comp_ename",
"display_ori_comp_ename",
"批件库生产厂商(英文)错误，由人工修改过的标记",
"ori_sfdanum",
"sfdanum_memo",
"split_sfdanum",
"genid",
"is_solvent",
"sol",
"sol_genid",
"sol_nameid",
"sol_name" };

        private void button3_Click(object sender, EventArgs e)
        {
            StringBuilder scriptBuilder = new StringBuilder();

            string[] selects = fields.Select(field => string.Format("IIf(IsNull({0}.[{1}]),'',{0}.[{1}]) AS {1}", "bd_product", field)).ToArray();


            var db = DbAccess.Db["bd_product"];
            //db.ExecuteNonQuery(script);
            //db.ExecuteNonQuery("ALTER TABLE identity ADD COLUMN pkid autoincrement(1,1) primary key");



            //DataTable dtSchema = new DataTable();
            //db.ExecuteConnection(item => dtSchema = item.GetSchema("Columns", new string[] { null, null, "bd_product" }));

            DataTable schema = db.ExecuteDataTable("select * from bd_product where 1=0", null, getSchema: true);


            string[] groups = fields.Select(field =>
            {
                string nullReplace = "''";
                if (schema.Columns.Contains(field) && schema.Columns[field].DataType.Name == "Int32")
                {
                    //&& schema.Columns[field].DataType.Name
                    Debug.WriteLine(schema.Columns[field].DataType.Name);
                    nullReplace = "0";
                }
                return string.Format("IIf(IsNull({0}.[{1}]),{2},{0}.[{1}])", "bd_product", field, nullReplace);
            }).ToArray();
            string script = string.Format("SELECT max(bd_product.mdid) AS mdid INTO identity FROM bd_product GROUP BY {0}", string.Join(",", groups));
            Debug.WriteLine(script);

        }
    }
}
